# -*- coding: utf-8 -*-
import json
import logging
from datetime import datetime, timedelta

from django.views.decorators.http import require_GET, require_POST
from django.views.generic import TemplateView
from django.utils.decorators import method_decorator

from common.stats import MG_BIGBANG_COLL as mg
from common.utils.tz import get_utc_date, utc_to_local_str
from common.utils.export import redirect_to_file, gen_filename
from common.utils.decorator import mongo_wrapper, response_wrapper
from common.utils.api import token_required
from common.utils.exceptions import ParamError
from common.stats import report as reportdb
from common.account.model.account import Account
from common.lottery import KEYWORD_TYPE_DCT
from common.platform.metis import db as metis_db

from tools import create_daily_report
from tools.bull_report import create_bull_report
from tools.fruit_report import create_fruit_report
from tools.kfc_report import create_kfc_report
from tools.lottery_report import create_lottery_report
from common.lottery import METIS_GAME_TYPE

_LOGGER = logging.getLogger(__name__)


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_daily_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    chn = query_dct.get('chn')
    filters = {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    if chn is not None:
        filters['chn'] = reportdb.convert_filter(chn)
        filters['day'] = filters.pop('_id')
        items = mg.channel_report.find(filters).sort("day")
    else:
        items = mg.daily_report.find(filters)
    resp_items = []
    for item in items:
        if item.get('new_device', 0) > 0:
            item['register_rate'] = round(float(item.get('new_register_device', 0)) / item['new_device'], 4)
            item['stay_2_rate'] = round(float(item.get('stay_2', 0)) / item['new_device'], 4)
        else:
            item['register_rate'] = item['new_recharge_rate'] =item['stay_2_rate'] = 0
        if item.get('new_user', 0) > 0:
            item['user_stay_2_rate'] = round(float(item.get('user_stay_2', 0)) / item['new_user'], 4)
            item['new_recharge_rate'] = round(float(item.get('new_recharge_user', 0)) / item['new_user'], 4)
        else:
            item['user_stay_2_rate'] = 0
        if item.get('active_user', 0) > 0:
            item['recharge_rate'] = round(float(item.get('recharge_user', 0)) / item['active_user'], 4)
        else:
            item['recharge_rate'] = 0
        resp_items.append(item)
    return {'list': resp_items}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_ks_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    filters = {
        'day': {'$gte': date_range[0], '$lte': date_range[1]},
        'index': 23
    }
    items = mg.interval_report.find(filters)
    resp_items = []
    for item in items:
        resp_items.append(item)

    return {'list': resp_items}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_profit_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    f = {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    items = mg.daily_report.find(f)
    kfc = list(mg.kfc_report.find(f))
    lottery = list(mg.lottery_report.find(f))
    bull = list(mg.bull_report.find(f))
    fruit = list(mg.fruit_report.find(f))


    resp_items = []
    total = {}
    for idx,item in enumerate(items):
        game_win_amount = kfc[idx].get('award_amount',0) + bull[idx].get('award_amount',0) +\
                     lottery[idx].get('win_amount',0) + fruit[idx].get('win_amount',0)
        game_bet_amount = kfc[idx].get('bet_amount',0) + bull[idx].get('bet_amount',0) + \
                     lottery[idx].get('bet_amount',0) + fruit[idx].get('bet_amount',0)
        metis_bet_amount = item.get('metis_price', 0)
        metis_profit = item.get('metis_profit', 0)
        data = {
            '_id': item['_id'],
            'profit_rate': round(item['profit_rate'], 4),
            'game_win_amount': game_win_amount,
            'game_bet_amount': game_bet_amount,
            'metis_profit': -metis_profit,
            'metis_bet_amount': metis_bet_amount,
        }
        for k in (
                'recharge_price', 'win_price', 'refund_price', 'bonus_price', 'pay_price', 'withdraw_price', 'profit',
                'coupon_use', 'system_recharge_price'):
            data[k] = item.get(k, 0)
            total.setdefault(k, 0)
            total[k] += item.get(k, 0)

        resp_items.append(data)
    for k, v in total.iteritems():
        if isinstance(v, dict):
            v['profit_rate'] = round(float(
                v['pay_amount'] - v['win_amount']) / v[
                                         'pay_amount'], 4) if v.get('pay_amount') else 0

    return {'list': resp_items, 'sum': total}


def _find_range(ranges, num):
    # 二分搜索数据所在区间，假设区间已经排序
    if not ranges:
        return None
    mid = len(ranges) / 2
    r = ranges[mid]
    if r[1] is not None:
        if num > r[1]:
            return _find_range(ranges[mid + 1:], num)
        elif num == r[1]:
            return r  # 落在此区间
        else:
            if r[0] is None:
                return r
            else:
                if num >= r[0]:
                    return r
                else:
                    return _find_range(ranges[:mid], num)
    else:
        if num >= r[0]:
            return r
        else:
            return _find_range(ranges[:mid], num)


def sub_activity_stats(item):
    activity_type = item.get('activity_type')
    date = item.get('date', '-')
    data = {
        'date': date,
        'activity_type': activity_type
    }
    pay = item.get('pay', {})
    win = item.get('win', {})
    bonus = item.get('bonus', {})
    refund = item.get('refund', {})
    if date >= '2018-04-26':
        new_logic = True
        # daily_pay = item.get('daily_pay', {})
        # daily_win = item.get('daily_win', {})
        # daily_bonus = item.get('daily_bonus', {})
    else:
        new_logic = False
        # daily_pay = pay
        # daily_win = win
        # daily_bonus = bonus
    data['pay_users_list'] = pay.get('users', [])
    data['win_users_list'] = win.get('users', [])
    data['pay_total'] = int(pay.get('total', 0))
    data['pay_users'] = len(pay.get('users', []))
    data['win_total'] = int(win.get('total', 0) + bonus.get('total', 0))
    data['win_users'] = len(win.get('users', []))
    data['refund_total'] = int(refund.get('total', 0))
    if new_logic:
        data['gain_total'] = data['pay_total'] - data['win_total']
    else:
        data['gain_total'] = data['pay_total'] - data['win_total'] - data['refund_total']
    return data


def sub_bet_type(item, selected_bet_type=None):
    datas = []
    pay = item.get('pay', {})
    win = item.get('win', {})
    bonus = item.get('bonus', {})
    activity_type = item.get('activity_type')
    date = item.get('date', '-')
    pay_key = [selected_bet_type] if selected_bet_type else pay.keys()
    for bet_type in pay_key:
        if (bet_type not in ['total', 'count', 'users']) and '_users' not in bet_type:
            data = dict({})
            data['activity_type'] = activity_type
            data['date'] = date
            data['pay_users'] = len(pay.get('{}_users'.format(bet_type), []))
            data['win_users'] = len(win.get('{}_users'.format(bet_type), []))
            data['bet_type'] = bet_type
            data['pay_total'] = int(pay.get(bet_type, 0))
            data['win_total'] = int(win.get(bet_type, 0) + bonus.get(bet_type, 0))
            data['gain_total'] = data['pay_total'] - data['win_total']
            if selected_bet_type:
                data['bet_type'] = selected_bet_type
            datas.append(data)
    return datas


SELECT_ALL_ACTIVITY_REPORT = "-1"
SELECT_ALL_BET_TYPE = "-1"
SELECT_ALL_DISTINCT_BET_TYPE = "-2"


@require_GET
@response_wrapper
@token_required
def get_activity_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    activity_types = query_dct.get('activity_type')
    bet_type = query_dct.get('bet_type')
    if activity_types == SELECT_ALL_ACTIVITY_REPORT:
        items = mg.activity_stats.find({"date": {
            '$gte': date_range[0], '$lte': date_range[1]}
        }).sort([('date', 1)])
    else:
        items = mg.activity_stats.find({"date": {
            '$gte': date_range[0], '$lte': date_range[1]
        },
            "activity_type": int(activity_types)
        })

    resp_data = []
    total = {}
    activity_details = {}
    total_pay_user_list = []
    total_win_user_list = []

    for item in items:
        activity_type = item.get('activity_type')
        pay = item.get('pay', {})
        data = None
        if bet_type == SELECT_ALL_BET_TYPE or (bet_type == SELECT_ALL_DISTINCT_BET_TYPE
                                               and activity_types == SELECT_ALL_ACTIVITY_REPORT):
            data = sub_activity_stats(item)
        elif bet_type in pay:
            reports = sub_bet_type(item, bet_type)
            report = reports[0] if reports else {}
            resp_data.append(report)
        elif bet_type == SELECT_ALL_DISTINCT_BET_TYPE:
            reports = sub_bet_type(item)
            for report in reports:
                resp_data.append(report)

        if data is not None:
            if activity_types == "-1" or bet_type == "-1":
                for k in ('pay_total', 'pay_users', 'win_total', 'win_users', 'refund_total', 'gain_total'):
                    total.setdefault(k, 0)
                    if k == 'pay_users':
                        total_pay_user_list = set(list(total_pay_user_list) + data['pay_users_list'])
                        total[k] = len(total_pay_user_list)
                    elif k == 'win_users':
                        total_win_user_list = set(list(total_win_user_list) + data['win_users_list'])
                        total[k] = len(total_win_user_list)
                    else:
                        total[k] += data[k]
                    activity_details.setdefault(data['activity_type'], {})
                    activity_details[activity_type].setdefault(k, 0)
                    activity_details[activity_type][k] += data[k]
            resp_data.append(data)

    if activity_types == "-1" and bet_type == "-1":  # 全不选,不分彩种，按照日期统计17
        aggr_data = {}
        pay_users_list = []
        win_users_list = []
        for data in resp_data:
            date = data['date']
            if date in aggr_data:
                for k in ('pay_total', 'pay_users', 'win_total', 'win_users', 'refund_total', 'gain_total'):
                    if k == 'pay_users':
                        pay_users_list = set(list(pay_users_list) + data['pay_users_list'])
                        aggr_data[date][k] = len(pay_users_list)
                    elif k == 'win_users':
                        win_users_list = set(list(win_users_list) + data['win_users_list'])
                        aggr_data[date][k] = len(win_users_list)
                    else:
                        aggr_data[date][k] += data[k]
            else:
                aggr_data[date] = {'date': date}
                if activity_types == "-1":
                    aggr_data[date]['activity_type'] = '-'
                else:
                    aggr_data[date]['activity_type'] = activity_type
                for k in ('pay_total', 'pay_users', 'win_total', 'win_users', 'refund_total', 'gain_total'):
                    if k == 'pay_users':
                        pay_users_list = data['pay_users_list']
                        aggr_data[date][k] = len(pay_users_list)
                    elif k == 'win_users':
                        win_users_list = data['win_users_list']
                        aggr_data[date][k] = len(win_users_list)
                    else:
                        aggr_data[date][k] = data[k]
        resp_data = aggr_data.values()
    return {'list': resp_data, 'sum': total, 'activity_details': activity_details}


@require_GET
@response_wrapper
@token_required
def get_daily_activity(req):
    query_dct = req.GET.dict()
    return reportdb.calc_activity_daily_report(query_dct)


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_recharge_report(req):
    query_dct = req.GET.dict()
    user_type = int(query_dct.get('user_type', -1))
    date_range = reportdb.get_date_range(query_dct, is_key=True)

    if date_range[0] == date_range[1]:
        key = date_range[0]
        table = mg.recharge_report
        if user_type == 1:
            # 新用户
            table = mg.fresh_recharge_report
        data = table.find_one({'_id': key}) or {}
        return data
    intervals = [(1, 2), (3, 10), (11, 50), (51, 100), (101, 500),
                 (501, 1000), (1001, 2000), (2001, 4000), (4001, 8000),
                 (8001, 15000), (15000, None)]
    if user_type == 1:
        items = mg.fresh_recharge_report.find({'_id': {
            '$gte': date_range[0],
            '$lte': date_range[1]
        }})
        data = {}
        # 新用户数据，直接聚合
        for item in items:
            for k, v in item.iteritems():
                if k == '_id':
                    data[k] = v
                elif isinstance(v, dict):
                    data.setdefault(k, {})
                    for ik, iv in v.iteritems():
                        data[k].setdefault(ik, 0)
                        data[k][ik] += iv
                else:
                    data.setdefault(k, 0)
                    data[k] += v
        return data
    else:
        # 所有用户数据, 只能实时聚合, 可能需要分页进行优化
        # 从daily_stats里面重新计算recharge_count, recharge_price,
        # gain_count, win_count, lose_count
        fields = ('recharge_count', 'recharge_price', 'gain_count',
                  'win_count', 'lose_count')
        data = {}
        for k in fields:
            data[k] = 0
        for k in intervals:
            key = '%s-%s' % (k[0], k[1])
            for f in fields:
                data.setdefault(key, {})[f] = 0
        date_range = reportdb.get_date_range(query_dct)
        items = mg.daily_stats.aggregate([
            {'$match': {'updated_at': {
                '$gte': date_range[0], '$lt': date_range[1]}}},
            {'$group': {'_id': '$user_id', 'win': {'$sum': '$win.total'},
                        'recharge': {'$sum': '$recharge.total'}}},
            {'$match': {'recharge': {'$gte': 1}}}
        ])
        for item in items:
            recharge = item.get('recharge')
            data['recharge_count'] += 1
            data['recharge_price'] += recharge
            interval = _find_range(intervals, recharge)
            key = '%s-%s' % (interval[0], interval[1])
            data[key]['recharge_count'] += 1
            data[key]['recharge_price'] += recharge
            win = item.get('win', 0)
            if win > 0:
                data['win_count'] += 1
                data[key]['win_count'] += 1
            if win - recharge > 0:
                data['gain_count'] += 1
                data[key]['gain_count'] += 1
            else:
                data['lose_count'] += 1
                data[key]['lose_count'] += 1

        return data


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_tactics_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct)
    recharge_range = query_dct.get('recharge_range', '')
    data = {
        'count': 0,
        'amount': 0
    }
    ranges = [(None, -10001), (-10000, -5001), (-5000, -2001), (-2000, -1001),
              (-1000, -501), (-500, -201), (-200, -1), (0, 200), (201, 500),
              (501, 1000), (1001, 2000), (2001, 5000), (5001, 10000),
              (10000, None)]
    for r in ranges:
        key = '%s ~ %s' % (r[0], r[1])
        data[key] = {
            'count': 0,
            'amount': 0
        }
    extra = {}
    if recharge_range:
        recharge_range = recharge_range.split(',')
        extra = {'recharge': {}}
        if len(recharge_range) == 1:
            extra['recharge']['$gte'] = int(recharge_range[0])
        else:
            extra['recharge']['$lte'] = int(recharge_range[1])
    condition = [
        {'$match': {'updated_at': {
            '$gte': date_range[0], '$lt': date_range[1]
        }}},
        {'$group': {'_id': '$user_id', 'gain': {'$sum': '$gain'},
                    'recharge': {'$sum': '$recharge.total'},
                    'pay': {'$sum': '$pay.total'}}},
    ]
    if extra:
        condition.append({'$match': {'$and': [
            {'pay': {'$gt': 0}},
            extra
        ]}})
    else:
        condition.append({'$match': {'pay': {'$gt': 0}}})
    t = mg.daily_stats.aggregate(condition)
    if not t.alive:
        t = []
    for item in t:
        gain = item.get('gain', 0)
        recharge = item.get('recharge', 0)
        data['count'] += 1
        data['amount'] += recharge
        interval = _find_range(ranges, int(gain))  # 有浮点数
        key = '%s ~ %s' % (interval[0], interval[1])
        data[key]['count'] += 1
        data[key]['amount'] += recharge

    # 整理成列表
    resp_items = []
    for k in ranges:
        key = '%s ~ %s' % (k[0], k[1])
        t = data[key]
        t['id'] = key
        resp_items.append(t)
    return {'list': resp_items,
            'total': {'count': data['count'], 'amount': data['amount']}}


def _get_active_user(date_range_str):
    data = {}
    au = mg.daily_report.find(
        {'_id': {'$gte': date_range_str[0], '$lte': date_range_str[1]}},
        {'active_user': 1})
    for k in au:
        data[k['_id']] = k['active_user']
    return data


COIN_TYPE = {
    'gen_dist': {1: u'充值', 2: u'夺宝币商品', 4: u'退款', 98: u'红包',
                 99: u'元宝兑换'},
    'cost_dist': {'fee': u'话费类', 'qb': u'Q币',
                  'wechat': u'微信红包类', 'coin': u'夺宝币类',
                  'credit': u'兑换成元宝'}
}

RESOURCE_DCT = {
    'coin': mg.coin_report,
    'credit': mg.credit_report,
    'coupon': mg.coupon_report
}


@mongo_wrapper
def _get_resource_report(resource, date_range):
    table = RESOURCE_DCT.get(resource)
    active_user = _get_active_user(date_range)
    items = table.find(
        {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    )
    resp_items = []
    for item in items:
        item.pop('gen_dist', None)
        item.pop('cost_dist', None)
        item['active_user'] = active_user.get(item['_id'], 0)
        resp_items.append(item)

    result = {
        'list': resp_items,
        'gen_dist': [],
        'cost_dist': []
    }
    for k in 'gen_dist', 'cost_dist':
        items = table.aggregate([
            {'$match': {'_id': {'$gte': date_range[
                0], '$lte': date_range[1]}}},
            {'$unwind': '$' + k},
            {'$group': {'_id': '$%s.type' % k,
                        'amount': {'$sum': '$%s.amount' % k}}}
        ])
        for item in items:
            t = item.get('_id')
            if t is None:
                t = u'其他'
            if resource == 'coin':
                t = COIN_TYPE[k].get(t, t)
            result[k].append(
                {'type': t, 'amount': item.get('amount', 0)})

        result[k].sort(key=lambda v: v['amount'])
    return result


@require_GET
@response_wrapper
@token_required
def get_resource_report(req, resource):
    if resource not in RESOURCE_DCT:
        raise ParamError('resource %s not exist!' % resource)
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    result = _get_resource_report(resource, date_range)
    return result


class ShippingReportView(TemplateView):
    def get(self, req):
        query_dct = req.GET.dict()
        date_range = reportdb.get_date_range(query_dct, is_key=True)
        return reportdb.get_shipping_report(date_range)

    def post(self, req):
        params = json.loads(req.body)
        start, end = reportdb.get_date_range(params, is_key=True)
        start = get_utc_date(start)
        end = get_utc_date(end)
        while start <= end:
            reportdb.refresh_shipping_report(start)
            start += timedelta(days=1)
        return {}

    @method_decorator(response_wrapper)
    @method_decorator(token_required)
    def dispatch(self, *args, **kwargs):
        return super(ShippingReportView, self).dispatch(*args, **kwargs)


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_top100_list(req):
    query_dct = req.GET.dict()
    export = query_dct.pop('$export', None)
    if export:
        filename = gen_filename('top100')
        header = ['user_id', 'chn', 'recharge', 'withdraw',
                  'real_gain', 'pay', 'win', 'bigbang_gain',
                  'dwc_bet', 'dwc_gain', 'bet', 'gain', 'gain_delta']
        cn_header = ['uid', u'渠道号', u'充值金额', u'提现金额', u'实际盈利', u'有效购彩',
                     u'购彩中奖', u'购彩盈利', u'电玩城参与', u'电玩城盈利', u'累计参与',
                     u'累计盈利', u'盈亏差值']
    date_range = reportdb.get_date_range(query_dct)
    top_type = query_dct.get('top_type', 'recharge')
    if top_type == 'total_lost':
        sort_type = 1
        top_type = 'total_gain'
    else:
        sort_type = -1
    chn = query_dct.get('chn')
    filters = [{
        '$match': {'updated_at': {
            '$gte': date_range[0],
            '$lt': date_range[1]
        }, '$or': [
            {'total_gain': {'$exists': 1}},
            {'pay': {'$exists': 1}},
            {'kfc': {'$exists': 1}},
            {'lottery': {'$exists': 1}},
            {'bull': {'$exists': 1}},
            {'fruit': {'$exists': 1}},
            {'metis': {'$exists': 1}},
        ]}},
        {'$group': {'_id': '$user_id',
                    'chn': {'$first': '$chn'},
                    'recharge': {'$sum': '$recharge.total'},
                    'pay': {'$sum': {'$subtract': [{"$ifNull": ["$pay.total", 0]}, {"$ifNull": ['$refund.total', 0]}]}},
                    'win': {'$sum': '$win.total'},
                    'bonus': {'$sum': '$bonus.total'},
                    'total_gain': {'$sum': '$total_gain'},
                    'bigbang_gain': {'$sum': '$gain.total'},
                    'bigbang_refund': {'$sum': '$refund.total'},
                    'withdraw': {'$sum': '$withdraw.total'},
                    'lottery_bet': {'$sum': '$lottery.bet_amount'},
                    'lottery_gain': {'$sum': '$lottery.gain_amount'},
                    'kfc_bet': {'$sum': '$kfc.bet_amount'},
                    'kfc_gain': {'$sum': '$kfc.gain_amount'},
                    'fruit_bet': {'$sum': '$fruit.bet_amount'},
                    'fruit_gain': {'$sum': '$fruit.gain_amount'},
                    'bull_bet': {'$sum': '$bull.bet_amount'},
                    'bull_gain': {'$sum': '$bull.gain_amount'},
                    'win_count': {'$sum': '$win.count'},
                    'metis_bet': {'$sum': '$metis.total_bet'},
                    'metis_gain': {'$sum': '$metis.total_profit'},
                    'system_award': {'$sum': {'$add': ['$system_recharge.total',
                                                       '$coupon.use']}},
                    'agent_award': {'$sum': {'$add': ['$agent_return.total_return',
                                                      '$agent.total_cp_daily_wage',
                                                      '$agent.total_cp_bonus',
                                                      '$agent.total_game_return',
                                                      '$agent.total_game_bonus']}}
                    }},
        {'$sort': {top_type: sort_type}},
    ]
    if chn is not None:
        filters[0]['$match']['chn'] = reportdb.convert_filter(chn)
    items = mg.daily_stats.aggregate(filters)
    resp_items = []
    rank = 0
    user_ids = []
    for item in items:
        user_ids.append(item['_id'])
        real_gain = item['withdraw'] - item['recharge']
        bigbang_gain = item['bigbang_gain'] + item['bigbang_refund']
        dwc_gain = item['lottery_gain'] + item['kfc_gain'] + item['fruit_gain'] + item['bull_gain']
        dwc_bet = item['lottery_bet'] + item['kfc_bet'] + item['fruit_bet'] + item['bull_bet'] 
        bigbang_pay = item['pay']
        rank += 1
        data = {
            'rank': rank,
            'chn': item['chn'],
            'user_id': item['_id'],
            'recharge': item['recharge'],
            'withdraw': item['withdraw'],
            'real_gain': real_gain,
            'pay': bigbang_pay,
            'win': item['win'] + item['bonus'],
            'bigbang_gain': bigbang_gain,
            'dwc_bet': dwc_bet,
            'dwc_gain': dwc_gain,
            'bet': dwc_bet + bigbang_pay + item['metis_bet'],
            'gain': dwc_gain + bigbang_gain + item['metis_gain'],
            'gain_delta': dwc_gain + bigbang_gain + item['metis_gain'] - real_gain,
            'metis_bet': item['metis_bet'],
            'metis_gain': item['metis_gain'],
        }
        if export:
            resp_items.append([data.get(x, '-') for x in header])
        else:
            resp_items.append(data)
    accounts = Account.query.filter(Account.id.in_(user_ids)).all()
    account_created_at_mapper = {account.id: account.created_at for account in accounts}
    create_date = {'date_range': query_dct.get('create_range')}
    create_range = reportdb.get_date_range(create_date, is_key=True)
    d_start = datetime.strptime(str(create_range[0]+' 00:00:00'), '%Y-%m-%d %H:%M:%S')
    d_end = datetime.strptime(str(create_range[1]+' 00:00:00'), '%Y-%m-%d %H:%M:%S')
    # _LOGGER.info('start:%s , end: %s', str(create_range[0]), str(create_range[1]))
    for data in resp_items[:]:
        if not (account_created_at_mapper.get(data.get('user_id'))):
            resp_items.remove(data)
        else:
            data['registered_at'] = utc_to_local_str(account_created_at_mapper.get(data.get('user_id')))
            d_r = datetime.strptime(str(data['registered_at']), '%Y-%m-%d %H:%M:%S')
            if d_start > d_r or d_end < d_r:
                resp_items.remove(data)

    if export:
        return redirect_to_file(resp_items, cn_header, filename)

    return {'list': resp_items}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_top_bigbang(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct)
    chn = query_dct.get('chn')
    search_type = query_dct.get('search_type', 'gain')  # search_type: pay || gain
    top_type = 'total_gain' if search_type == 'gain' else 'total_pay'
    cond = {
        'updated_at': {
            '$gte': date_range[0],
            '$lt': date_range[1]
        }, '$or': [
            {'total_gain': {'$exists': 1}},
            {'pay': {'$exists': 1}},
            {'kfc': {'$exists': 1}},
            {'lottery': {'$exists': 1}},
            {'bull': {'$exists': 1}},
            {'fruit': {'$exists': 1}},
            {'metis': {'$exists': 1}}]}
    if chn:
        cond['chn'] = json.loads(chn)

    group = dict({
        '_id': '$user_id',
        'chn': {'$first': '$chn'},
        'total_pay': {'$sum': {'$subtract': [
            {'$add': [
                {"$ifNull": ["$metis.total_bet", 0]},
                {"$ifNull": ["$pay.total", 0]},
                {"$ifNull": ["$lottery.bet_amount", 0]},
                {"$ifNull": ["$kfc.bet_amount", 0]},
                {"$ifNull": ["$fruit.bet_amount", 0]},
                {"$ifNull": ["$bull.bet_amount", 0]},
            ]},
            {"$ifNull": ["$refund.total", 0]},
        ]}},
        'total_gain': {'$sum': {'$add': [
            {"$ifNull": ["$metis.total_profit", 0]},
            {"$ifNull": ['$gain.total', 0]},
            {"$ifNull": ["$lottery.gain_amount", 0]},
            {"$ifNull": ["$kfc.gain_amount", 0]},
            {"$ifNull": ["$fruit.gain_amount", 0]},
            {"$ifNull": ["$bull.gain_amount", 0]},
        ]}}
    })
    for key, value in KEYWORD_TYPE_DCT.iteritems():
        group[key] = {'$sum': {'$subtract': [
                                {'$add': [
                                    {"$ifNull": ["$win.{}".format(value), 0]},
                                    {"$ifNull": ["$refund.{}".format(value), 0]},
                                    {"$ifNull": ["$bonus.{}".format(value), 0]},
                                ]},
                                {"$ifNull": ["$pay.{}".format(value), 0]}]} \
            if search_type == 'gain' else '$pay.{}'.format(value)}
        if search_type == 'pay':
            group[key + '_refund'] = {'$sum': '$refund.{}'.format(value)}
    for key in ['lottery', 'kfc', 'bull', 'fruit']:
        group[key] = {'$sum': '${}.gain_amount'.format(key)} if search_type == 'gain' else {
            '$sum': '${}.bet_amount'.format(key)}
    for key in METIS_GAME_TYPE.to_dict():
        group['metis' + str(key)] = {'$sum': '$metis.{}_total_profit'.format(str(key))} if search_type == 'gain' else {
            '$sum': '$metis.{}_bet_amount'.format(key)}
    items = mg.daily_stats.aggregate([
        {'$match': cond},
        {'$group': group},
        {'$sort': {top_type: -1}}
    ])
    resp_items, user_ids = [], []
    rank = 0
    for item in items:
        for key, value in KEYWORD_TYPE_DCT.iteritems():
            item[key] = item[key] - item.get(key + '_refund', 0)
        user_ids.append(item['_id'])
        rank += 1
        item['rank'] = rank
        resp_items.append(item)
    accounts = Account.query.filter(Account.id.in_(user_ids)).all()
    account_created_at_mapper = {account.id: account.created_at for account in accounts}
    create_date = {'date_range': query_dct.get('create_range')}
    create_range = reportdb.get_date_range(create_date, is_key=True)
    d_start = datetime.strptime(str(create_range[0]+' 00:00:00'), '%Y-%m-%d %H:%M:%S')
    d_end = datetime.strptime(str(create_range[1]+' 00:00:00'), '%Y-%m-%d %H:%M:%S')
    for data in resp_items[:]:
        if not (account_created_at_mapper.get(data.get('_id'))):
            resp_items.remove(data)
        else:
            data['registered_at'] = utc_to_local_str(account_created_at_mapper.get(data.get('_id')))
            d_r = datetime.strptime(str(data['registered_at']), '%Y-%m-%d %H:%M:%S')
            if d_start > d_r or d_end < d_r:
                resp_items.remove(data)
    return {'list': resp_items}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_lottery_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    resp_items = []
    f = {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    items = mg.lottery_report.find(f)
    for item in items:
        resp_items.append(item)

    # 分布数据需要进行汇总
    group = {'_id': None}
    for k in range(6):
        group['win_index_%s' % k] = {'$sum': '$win_index_%s' % k}
        for suffix in 'count', 'amount':
            field = 'index_%s_%s' % (k, suffix)
            group[field] = {'$sum': '$%s' % field}
    for k in (0, 1, 10, 100):
        field = 'amount_%s' % k
        group[field] = {'$sum': '$%s' % field}
    t = mg.lottery_report.aggregate([
        {'$match': f},
        {'$group': group}
    ])
    t = t.next() if t.alive else {}
    t.pop('_id', None)
    return {'list': resp_items, 'dist': t}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_fruit_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    resp_items = []
    f = {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    items = mg.fruit_report.find(f)
    for item in items:
        resp_items.append(item)

    # 分布数据需要进行汇总
    group = {'_id': None}
    for k in range(25):
        if k == 3:
            continue
        group['win_index_%s' % k] = {'$sum': '$win_index_%s' % k}
        for suffix in 'count', 'amount':
            field = 'index_%s_%s' % (k, suffix)
            group[field] = {'$sum': '$%s' % field}
    for k in (0, 1, 10, 100, 500, 1000):
        field = 'amount_%s' % k
        group[field] = {'$sum': '$%s' % field}
    t = mg.fruit_report.aggregate([
        {'$match': f},
        {'$group': group}
    ])
    t = t.next() if t.alive else {}
    t.pop('_id', None)
    return {'list': resp_items, 'dist': t}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_bull_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    resp_items = []
    f = {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    items = mg.bull_report.find(f)
    for item in items:
        resp_items.append(item)

    # 分布数据需要进行汇总
    group = {'_id': None}
    for k in (1, 2, 4, 8):
        group['win_idx_%s' % k] = {'$sum': '$win_idx_%s' % k}
        for suffix in 'count', 'amount':
            field = 'index_%s_%s' % (k, suffix)
            group[field] = {'$sum': '$%s' % field}
    for k in (0, 1, 10, 100, 500, 1000):
        field = 'amount_%s' % k
        group[field] = {'$sum': '$%s' % field}
    t = mg.bull_report.aggregate([
        {'$match': f},
        {'$group': group}
    ])
    t = t.next() if t.alive else {}
    t.pop('_id', None)
    return {'list': resp_items, 'dist': t}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_kfc_report(req):
    query_dct = req.GET.dict()
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    resp_items = []
    f = {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    items = mg.kfc_report.find(f)
    for item in items:
        resp_items.append(item)

    # 分布数据需要进行汇总
    group = {'_id': None}
    for k in range(3):
        for suffix in 'count', 'bet', 'award':
            field = 'gamestatus_%s_%s' % (k+1, suffix)
            group[field] = {'$sum': '$%s' % field}
    for k in (0, 1, 10, 100):
        field = 'amount_%s' % k
        group[field] = {'$sum': '$%s' % field}
    t = mg.kfc_report.aggregate([
        {'$match': f},
        {'$group': group}
    ])
    t = t.next() if t.alive else {}
    t.pop('_id', None)
    return {'list': resp_items, 'dist': t}


@require_POST
@response_wrapper
@token_required
def fresh(req):
    create_daily_report.main(0)
    return {}


@require_POST
@response_wrapper
@token_required
def fresh_bull(req):
    today = get_utc_date()
    create_bull_report(today)
    return {}


@require_POST
@response_wrapper
@token_required
def fresh_fruit(req):
    today = get_utc_date()
    create_fruit_report(today)
    return {}


@require_POST
@response_wrapper
@token_required
def fresh_kfc(req):
    today = get_utc_date()
    create_kfc_report(today)
    return {}


@require_POST
@response_wrapper
@token_required
def fresh_lottery(req):
    today = get_utc_date()
    create_lottery_report(today)
    return {}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_interval_recharge(req):
    query_dct = req.GET.dict()
    user_type = int(query_dct.get('user_type', -1))
    date_range = reportdb.get_date_range(query_dct, is_key=True)

    if date_range[0] == date_range[1]:
        key = date_range[0]
        table = mg.daily_charge_report
        l = []
        items = table.find({'day': key}).sort('index', 1)
        for item in items:
            l.append({
                'index': item['index'],
                'end': '%s:00~%s:00' % (item['index'], item['index']+1),
                'recharge_price': item['recharge_price'],
                'recharge_user': item['recharge_user'],
                'delta_price': item['delta_price'],
                'ddz_online': item.get('ddz_online', 0),
                'zjh_online': item.get('zjh_online', 0),
                'blood_online': item.get('blood_online', 0),
            })
        return {'list': l}
    items = mg.daily_charge_report.aggregate([
        {'$match': {'day': {'$gte': date_range[0], '$lte': date_range[1]}}},
        {'$group': {'_id': '$index', 'avg_price': {'$avg': '$delta_price'},
            'recharge_price': {'$avg': '$recharge_price'}}}
    ])
    l = []
    for i in range(0,24):
        l.append({
            'index': i,
            'end': '%s:00~%s:00' % (i, i+1),
            'delta_price': 0,
            'recharge_price': 0,
        })
    for item in items:
        l[int(item['_id'])].update({
            'delta_price': item['avg_price'],
            'recharge_price': item['recharge_price']
        })
    return {'list': l}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_metis_report(req):
    query_dct = req.GET.dict()
    game_id = query_dct['game_id']
    date_range = reportdb.get_date_range(query_dct)
    items = mg.daily_stats.aggregate([
        {'$match': {'$and':[
            {'updated_at': {
                '$gte': date_range[0],
                '$lt': date_range[1]}},
            {'metis.{}_bet_amount'.format(game_id): {'$exists': 1}}
        ]}},
        {"$project": {
            "metis": 1,
            "date": {
                '$dateToString': {
                    'format': '%Y-%m-%d',
                    'date': {'$add': ['$updated_at', 8 * 3600000]},
                }
            }}},
        {'$group': {"_id": '$date',
                    "total": {"$sum": 1},
                    'total_bet': {'$sum': '$metis.{}_bet_amount'.format(game_id)},
                    'total_profit': {'$sum': '$metis.{}_total_profit'.format(game_id)}}},
        {'$sort': {'_id': -1}},
    ])
    resp_items = []
    for item in items:
        total_bet = item.get('total_bet', 0)
        total_profit = item.get('total_profit', 0) * -1
        if not total_bet:
            continue
        date = item['_id']
        count = item['total']
        water_rate, water_pool = metis_db.get_pool(game_id, date)
        data = {
            'date': date,
            'count': count,
            'bet_amount': total_bet,  # 下注额
            'total_profit': total_profit,  # 当前抽水金额
            'now_rate': round((total_profit / total_bet) * 100, 2),  # 当前抽水率
            'control_rate': water_rate * 100,  # 预期抽水率, 0.055 = 5.5 %
        }
        resp_items.append(data)

    return {'list': resp_items}


@require_GET
@response_wrapper
@token_required
@mongo_wrapper
def get_game_list_report(req):
    query_dct = req.GET.dict()
    game_filter = json.loads(query_dct.get('game_filter') or '[]')
    by_sum = True if not game_filter else False
    by_new_user = int(query_dct.get('by_new_user') or 0)
    date_range = reportdb.get_date_range(query_dct, is_key=True)
    f = {'_id': {'$gte': date_range[0], '$lte': date_range[1]}}
    metis = mg.metis_report.find(f)
    kfc = mg.kfc_report.find(f)
    fruit = mg.fruit_report.find(f)
    lottery = mg.lottery_report.find(f)
    bull = mg.bull_report.find(f)

    list_key = ['user_count', 'gain_count', 'lose_count', 'banker_person']
    total = {}
    resp_items, sum_items = [], []

    for idx, item in enumerate(metis):
        date_items = []
        lottery_stats = lottery[idx].get('new_user', {}) if by_new_user else lottery[idx]
        kfc_stats = kfc[idx].get('new_user', {}) if by_new_user else kfc[idx]
        fruit_stats = fruit[idx].get('new_user', {}) if by_new_user else fruit[idx]
        bull_stats = bull[idx].get('new_user', {}) if by_new_user else bull[idx]

        game_data = {
            'lottery': {'date': item['_id'], 'type': 'lottery',
                        'user_count': lottery_stats.get('user_id_list', {}).get('bet_user_id', []),
                        'total_bet': lottery_stats.get('bet_amount', 0),
                        'total_profit': -1 * (lottery_stats.get('gain_amount', 0) + lottery_stats.get('lose_amount', 0)),
                        'sys_bet_amount': lottery_stats.get('bet_amount', 0),
                        'system_original_gain': lottery_stats.get('bet_amount', 0) - lottery_stats.get('origin_award', 0),
                        'gain_count': lottery_stats.get('user_id_list', {}).get('gain_user_id', []),
                        'gain_amount': lottery_stats.get('gain_amount', 0),
                        'lose_count': lottery_stats.get('user_id_list', {}).get('lose_user_id', []),
                        'lose_amount': lottery_stats.get('lose_amount', 0),
                        },
            'kfc': {'date': item['_id'], 'type': 'kfc',
                    'user_count': kfc_stats.get('user_id_list', {}).get('bet_user_id', []),
                    'total_bet': kfc_stats.get('bet_amount', 0),
                    'total_profit': -1 * (kfc_stats.get('gain_amount', 0) + kfc_stats.get('lose_amount', 0)),
                    'sys_bet_amount': kfc_stats.get('bet_amount', 0),
                    'system_original_gain': kfc_stats.get('bet_amount', 0) - kfc_stats.get('origin_award', 0),
                    'gain_count': kfc_stats.get('user_id_list', {}).get('gain_user_id', []),
                    'gain_amount': kfc_stats.get('gain_amount', 0),
                    'lose_count': kfc_stats.get('user_id_list', {}).get('lose_user_id', []),
                    'lose_amount': kfc_stats.get('lose_amount', 0),
                    },
            'fruit': {'date': item['_id'], 'type': 'fruit',
                      'user_count': fruit_stats.get('user_id_list', {}).get('bet_user_id', []),
                      'total_bet': fruit_stats.get('bet_amount', 0),
                      'total_profit': -1 * (fruit_stats.get('gain_amount', 0) + fruit_stats.get('lose_amount', 0)),
                      'sys_bet_amount': fruit_stats.get('bet_amount', 0),
                      'system_original_gain': fruit_stats.get('bet_amount', 0) - fruit_stats.get('origin_award', 0),
                      'gain_count': fruit_stats.get('user_id_list', {}).get('gain_user_id', []),
                      'gain_amount': fruit_stats.get('gain_amount', 0),
                      'lose_count': fruit_stats.get('user_id_list', {}).get('lose_user_id', []),
                      'lose_amount': fruit_stats.get('lose_amount', 0),
                      },
            'bull': {'date': item['_id'], 'type': 'bull',
                     'user_count': bull_stats.get('user_id_list', {}).get('bet_user_id', []),
                     'total_bet': bull_stats.get('bet_amount', 0),
                     'total_profit': -1 * (bull_stats.get('gain_amount', 0) + bull_stats.get('lose_amount', 0)),
                     'sys_bet_amount': bull_stats.get('sys_bet_amount', 0),
                     'system_original_gain': (bull_stats.get('sys_bet_amount', 0) -
                                              (bull_stats.get('origin_award', 0) - bull_stats.get('player_award_amount', 0)
                                               - bull_stats.get('extra_choushui',0))),
                     'gain_count': bull_stats.get('user_id_list', {}).get('gain_user_id', []),
                     'gain_amount': bull_stats.get('gain_amount', 0),
                     'lose_count': bull_stats.get('user_id_list', {}).get('lose_user_id', []),
                     'lose_amount': bull_stats.get('lose_amount', 0),
                     'banker_person': bull_stats.get('user_id_list', {}).get('banker_user_id', []),
                     'banker_bet': bull_stats.get('player_bet_amount', 0),
                     'banker_gain': bull_stats.get('player_bet_amount', 0) - bull_stats.get('player_award_amount', 0),
                     'banker_system_gain': (bull_stats.get('player_bet_amount', 0) -
                                            bull_stats.get('player_award_amount', 0)) * 0.03,
                     'banker_count': bull_stats.get('banker_count', 0)}
        }
        if not game_filter:
            date_items.append(game_data['lottery'])
            date_items.append(game_data['bull'])
            date_items.append(game_data['fruit'])
            date_items.append(game_data['kfc'])
        else:
            for f in game_filter:
                if f in ['lottery', 'bull', 'fruit', 'kfc']:
                    date_items.append(game_data.get(f, {}))
        for game_id in METIS_GAME_TYPE.values():
            if game_filter and str(game_id) not in game_filter:
                continue
            if game_id != 5000:
                data = dict({})
                data['type'] = 'metis_' + str(game_id)
                data['date'] = item['_id']
                if by_new_user:
                    data.update(item[str(game_id)].get('new_user', {}))
                else:
                    data.update(item[str(game_id)])
                date_items.append(data)

        sum_dct = {}
        for key in ['total_bet', 'total_profit', 'sys_bet_amount', 'system_original_gain', 'gain_count', 'lose_count',
                    'gain_amount', 'lose_amount', 'banker_person', 'banker_bet', 'banker_gain', 'banker_system_gain',
                    'user_count', 'banker_count']:
            for idx, item in enumerate(date_items):
                if key in list_key:
                    sum_dct[key] = list(set(sum_dct[key] + item.get(key, []))) if key in sum_dct else item.get(key, [])
                    if key in item:
                        date_items[idx][key] = len(item[key])
                else:
                    sum_dct[key] = sum_dct[key] + item.get(key, 0) if key in sum_dct else item.get(key, 0)
            if key in list_key:
                sum_dct[key] = len(sum_dct[key])
            sum_dct['date'] = item['date']
            sum_dct['type'] = u'汇总'
        sum_dct['user_count'] = sum_dct['gain_count'] + sum_dct['lose_count']
        sum_items.append(sum_dct)
        resp_items = resp_items + date_items
    if by_sum:
        resp_items = sum_items
    return {'list': resp_items, 'sum': total}
